﻿<?xml version="1.0" encoding="utf-8"?><Articles><Article><ArticlesID>867</ArticlesID><Title>Import dữ liệu từ file Excel sử dụng ASP.NET</Title><Body>&amp;lt;p&amp;gt;Đoạn code sau sử dụng asp.net để import to&amp;amp;agrave;n bộ dữ liệu từ file Excel&amp;lt;/p&amp;gt;
&amp;lt;pre title=&amp;quot;code&amp;quot; class=&amp;quot;brush: csharp;&amp;quot;&amp;gt;
using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Data.OleDb;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Collections;

namespace ExcelHelper
{
   public class LoadExcel
   {
       private static string OpenExcelFile(string fPath)
       {
           string connectionstring = String.Empty;
           string[] splitdot = fPath.Split(new char[1] { '.' });
           string dot = splitdot[splitdot.Length - 1].ToLower();
           if (dot == &amp;amp;quot;xls&amp;amp;quot;)
           {
               //tao chuoi ket noi voi Excel 2003
               connectionstring = &amp;amp;quot;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=&amp;amp;quot; + fPath + &amp;amp;quot;;Extended Properties=\&amp;amp;quot;Excel 8.0;HDR=Yes;IMEX=1\&amp;amp;quot;&amp;amp;quot;;
           }
           else if (dot == &amp;amp;quot;xlsx&amp;amp;quot;)
           {
               //tao chuoi ket noi voi Excel 2007
               connectionstring = &amp;amp;quot;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=&amp;amp;quot; + fPath + &amp;amp;quot;;Extended Properties=\&amp;amp;quot;Excel 8.0;HDR=YES;IMEX=1\&amp;amp;quot;&amp;amp;quot;;
           }
           return connectionstring;
       }
       /// &amp;amp;lt;summary&amp;amp;gt;
       /// Uploads the file.
       /// &amp;amp;lt;/summary&amp;amp;gt;
       /// &amp;amp;lt;param name=&amp;amp;quot;fUpload&amp;amp;quot;&amp;amp;gt;The upload control .&amp;amp;lt;/param&amp;amp;gt;
       /// &amp;amp;lt;param name=&amp;amp;quot;fPath&amp;amp;quot;&amp;amp;gt;The path string.&amp;amp;lt;/param&amp;amp;gt;
       public static string UploadFile(FileUpload fUpload)
       {
           string fPath = String.Empty;
           try
           {
               fPath = HttpContext.Current.Server.MapPath(&amp;amp;quot;~/upload/temp/&amp;amp;quot; + fUpload.FileName);
               FileInfo fInfo = new FileInfo(fPath);
               if (fInfo.Exists)
               {
                   fInfo.Delete();
               }
               fUpload.SaveAs(fPath);
           }
           catch (Exception ex)
           {
               throw new Exception(ex.ToString());
           }
           return fPath;
       }
       /// &amp;amp;lt;summary&amp;amp;gt;
       /// Gets the name of the sheet.
       /// &amp;amp;lt;/summary&amp;amp;gt;
       /// &amp;amp;lt;param name=&amp;amp;quot;fPath&amp;amp;quot;&amp;amp;gt;The f path.&amp;amp;lt;/param&amp;amp;gt;
       /// &amp;amp;lt;returns&amp;amp;gt;return all sheet name&amp;amp;lt;/returns&amp;amp;gt;
       public static ArrayList GetSheetName(string fPath)
       {
           ArrayList sheetnames = new ArrayList();
           string connectionstring = OpenExcelFile(fPath);
           //mo ket noi den file excel
           OleDbConnection cnn = new OleDbConnection(connectionstring);
           cnn.Open();

           //tao bang luu tru tam cac du lieu trong file 
           DataTable table = new DataTable();
           table = cnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
           //doc tung dong trong bang luu tru tam
           for (int i = 0; i &amp;amp;lt; table.Rows.Count; i++)
           {
               string name = table.Rows[i][2].ToString().Replace(&amp;amp;quot;'&amp;amp;quot;, &amp;amp;quot;&amp;amp;quot;);//get ten tung sheet co trong bang luu tru
               //kiem tra sheet
               if (naEndsWith(&amp;amp;quot;$&amp;amp;quot;))
               {
                   sheetnames.Add(naReplace(&amp;amp;quot;$&amp;amp;quot;, &amp;amp;quot;&amp;amp;quot;));
               }
           }
           cnn.Close();
           table.Dispose();
           return sheetnames;
       }
       /// &amp;amp;lt;summary&amp;amp;gt;
       /// Gets the data excel.
       /// &amp;amp;lt;/summary&amp;amp;gt;
       /// &amp;amp;lt;param name=&amp;amp;quot;fPath&amp;amp;quot;&amp;amp;gt;The f path.&amp;amp;lt;/param&amp;amp;gt;
       /// &amp;amp;lt;param name=&amp;amp;quot;sheetname&amp;amp;quot;&amp;amp;gt;The sheetna&amp;amp;lt;/param&amp;amp;gt;
       /// &amp;amp;lt;returns&amp;amp;gt;return dataset&amp;amp;lt;/returns&amp;amp;gt;
       public static DataSet GetDataExcel(string fPath, string sheetname)
       {
           DataSet ds = new DataSet();
           string connectionstring = OpenExcelFile(fPath);
           string query = &amp;amp;quot;SELECT * FROM [&amp;amp;quot; + sheetname + &amp;amp;quot;$]&amp;amp;quot;;
           using (OleDbConnection cnn = new OleDbConnection(connectionstring))
           {
               cnn.Open();
               OleDbDataAdapter oleAdapter = new OleDbDataAdapter(query, cnn);
               oleAdapter.Fill(ds, sheetname);
               oleAdapter.Dispose();
               cnn.Close();
               cnn.Dispose();
           }
           return ds;
       }
   }
}
&amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;&amp;amp;nbsp;&amp;lt;/p&amp;gt;
&amp;lt;p style=&amp;quot;text-align: right;&amp;quot;&amp;gt;&amp;lt;em&amp;gt;Theo anhhai680 - aspvn.net&amp;lt;/em&amp;gt;&amp;lt;/p&amp;gt;</Body><CommentEnabled>True</CommentEnabled><ViewCount>0</ViewCount><ReleaseDate>4/16/2010 9:59:11 AM</ReleaseDate><TotalComment>0</TotalComment><IsMedia>False</IsMedia><IsPicture>False</IsPicture><IsVote>True</IsVote><ArticlePassword /><PostVisible>0</PostVisible><ArticlePath>/2010/3/24/Import-du-lieu-tu-file-Excel-su-dung-ASP-NET-363</ArticlePath><Approved>True</Approved><ImagePath /><CategoryId>26</CategoryId><ArrayCatID>26|25</ArrayCatID></Article></Articles>